﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class TrinhDoDAL
    {
        public static List<TrinhDoPublic> LoadTrinhDo()
        {
            var trinhdo = Common.db.ExecuteStoreQuery<TrinhDoPublic>(@"select * from TrinhDo");
            return trinhdo.ToList();
        }
        public static TrinhDoPublic GetTrinhDoByMaTD(int MaTD)
        {
            var trinhdo = Common.db.ExecuteStoreQuery<TrinhDoPublic>(@"select TenTrinhDo from TrinhDo where MaTD=" + MaTD);
            return trinhdo.FirstOrDefault();
        }
        public static List<TrinhDoPublic> LoadBieuDoTrinhDo()
        {
            string chuoi = @"select first.MaTD, first.TenTrinhDo, first.SL
                        from (
	                        select top 5 T.MaTD,TenTrinhDo,COUNT(N.MaTD) as SL
	                        from TrinhDo T join Nhankhau N
	                        on T.MaTD = N.MaTD
	                        group by T.MaTD, TenTrinhDo
	                        order by SL desc) first
	
                        union all
	
                        select last.MaTD, last.TenTrinhDo, last.SL
                        from (
	                        select cast(0 as tinyint) as MaTD, 'Khác' as TenTrinhDo,ISNULL(sum(t2.SL),0) as SL
	                        FROM
	                        (   SELECT ROW_NUMBER() OVER(ORDER BY SL desc) AS row, t1.*
		                        FROM
		                        ( 	select COUNT(N.MaTD) as SL
			                        from TrinhDo T left join Nhankhau N
			                        on T.MaTD = N.MaTD
			                        group by T.MaTD
		                        ) t1
	                        ) t2
	                        WHERE t2.row BETWEEN 6 AND 100) last; 
                        ";
            var td = Common.db.ExecuteStoreQuery<TrinhDoPublic>(chuoi);
            return td.ToList();
        }
        public static List<TrinhDoPublic> ReportTD()
        {
            string chuoi = @"select td.MaTD,td.TenTrinhDo, COUNT(nk.MaNK) as SL, cast(left((COUNT(nk.MaNK)*100.0/(select COUNT(*) from Nhankhau)),5) as float) as TyLe 
from NhanKhau nk right join TrinhDo td on nk.MaTD = td.MaTD and nk.NgayXoa is null 
group by td.MaTD, td.TenTrinhDo";
            var td = Common.db.ExecuteStoreQuery<TrinhDoPublic>(chuoi);
            return td.ToList();
        }
    }
}
